/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import Enum.Frequency;
import Model.ChoreBean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author wrjohns
 */
public class ChoreListDAO 
{
    private static Connection DBConn;

    public static void init() throws SQLException 
    {
        try 
        {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
        } 
        catch (ClassNotFoundException e) 
        {
            System.err.println(e.getMessage());
            System.exit(0);
        }
        DBConn = DriverManager.getConnection("jdbc:derby://localhost:1527/ChoreBoard");
    }

    public static void terminate() throws SQLException 
    {
        DBConn.close();
    }
    
    public static List<ChoreBean> getChores(String houseName) throws SQLException
    {
        ArrayList<ChoreBean> ret = new ArrayList<ChoreBean>();
    
        Statement stmt = DBConn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from chore where housename='" + houseName + "'");
        
         while (rs.next()) 
         {
             
             Frequency freq = null;
             String check = rs.getString("frequency");
             if(check != null)
             {
                switch (check.toUpperCase())
                {
                    case "DAILY":
                        freq = Frequency.DAILY;
                        break;
                    case "WEEKLY":
                        freq = Frequency.WEEKLY;
                        break;
                    case "BIWEEKLY":
                        freq = Frequency.BIWEEKLY;
                        break;
                    case "MONTHLY":
                        freq = Frequency.MONTHLY;
                        break;
                    case "YEARLY":
                        freq = Frequency.YEARLY;
                        break;
                }
             }
             
             ret.add(new ChoreBean(rs.getString("chorename"),rs.getString("housename"),1,1,1,null,1));
                     
             
         }
        return ret;
    }
    
    public static List<ChoreBean> getWeeklyChores(String houseName) throws SQLException
    {
        ArrayList<ChoreBean> ret = new ArrayList<ChoreBean>();
        Statement stmt = DBConn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from chore where housename='" + houseName + "' AND frequency = 'WEEKLY'");
        while(rs.next())
        {
            ret.add(new ChoreBean(rs.getString("chorename"), rs.getString("housename"),rs.getInt("startday"),
                                rs.getInt("startmonth"), rs.getInt("startYear"), Frequency.WEEKLY, rs.getInt("priority")));
        }
        return ret;
    }
    
    public static List<ChoreBean> getChores(int day, int month, int year, String houseName) throws SQLException
    {
        ArrayList<ChoreBean> ret = new ArrayList<ChoreBean>();
    
        Statement stmt = DBConn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from chore where housename='" + houseName + "'");
        while(rs.next())
        {
            String freq = rs.getString("frequency").toUpperCase();
            switch (freq)
            {
                case "DAILY":
                   ret.add(new ChoreBean(rs.getString("chorename"),rs.getString("housename"),rs.getInt("startday"),
                     rs.getInt("startmonth"), rs.getInt("startYear"), Frequency.DAILY, rs.getInt("priority")));
                    break;
                case "WEEKLY":
                    break;
                case "BIWEEKLY":
                    break;
                case "MONTHLY":
                    if(day == rs.getInt("startday"))
                    {
                        ret.add(new ChoreBean(rs.getString("chorename"), rs.getString("housename"),rs.getInt("startday"),
                                rs.getInt("startmonth"), rs.getInt("startYear"), Frequency.MONTHLY, rs.getInt("priority")));
                    }
                    break;
                case "YEARLY":
                    if(day == rs.getInt("startday") && month == rs.getInt("startmonth"))
                    {
                        ret.add(new ChoreBean(rs.getString("chorename"), rs.getString("housename"),rs.getInt("startday"),
                                rs.getInt("startmonth"), rs.getInt("startYear"), Frequency.YEARLY, rs.getInt("priority")));
                    }
                    break;
                default:
                    ret.add(new ChoreBean(rs.getString("chorename"), rs.getString("housename"),rs.getInt("startday"),
                                rs.getInt("startmonth"), rs.getInt("startYear"), null, rs.getInt("priority")));
                    break;
            }
        }
        return ret;
    }
}
